Mega Code Archive
Character String Functions
1) A retrieval with no third argument
2) ASCII(0), ASCII(9)
3) ASCII(A) ASCII(Z) ASCII(A) ASCII(Z) ASCII(ABC)
4) ASCII(x) gets the ASCII value of the character x
5) Ascii gives the ASCII value of the first character of a string
6) Call RPAD function in PLSQL
7) Capitalized the employee name
8) Change book title to upper case if it is not
9) Change column data to upper case
10) Character Functions
11) Characters rather than spaces are trimmed
12) CHR(x) gets the character with the ASCII value of x
13) Combine DECODE and SUBSTR together
14) Combine SUBSTR and INSTR to deal with a table column
15) Combine upper function with LIKE
16) Combining Functions
17) Compare char value without knowing the case
18) Compare substring in where statement
19) Compose(de unistr(0300) )
20) Concat concatenates two strings
21) CONCAT(x, y) appends y to x
22) CONTAINS(description, 10g near oracle) 0
23) CONTAINS(description, website) 3
24) Display job name in lower case
25) Display the position where the second occurrence of e occurs in employees first name
26) Dump(90101,16)
27) Dump(reverse(90101),16)
28) Example SELECT output using SUBSTR()
29) If begin is negative, then retrieval occurs from the right-hand side of original string
30) If search pattern is not in the string, the INSTR function returns 0
31) If the INSTR pattern is not found, then the entire string would be returned
32) If the look for string is not present, then the replacing does not occur
33) Initcap changes the first (initial) letter of a word (string) or series of words into uppercase
34) INSTR (in-string) is a function used to find patterns in strings
35) Instr and substr
36) INSTR returns a location within the string where search pattern begins
37) INSTR(x, find_string [, start] [, occurrence]) searchs for find_string in x
38) Left padding the name of department
39) LENGTH(x) get the number of characters in x
40) Look for the second occurrence of is
41) LOWER(x) converts the letters in x to lowercase
42) LPAD() with number column
43) LTRIM(x [, trim_string]) trim characters from the left of x
44) Ltrim removes a set of characters from the left of a string
45) Manipulating Strings
46) Match trimmed string
47) Nested trim
48) Order by the length of employee name
49) Padding based on value length
50) Padding department with and
51) Proximity searches with contains
52) Retrieval begins at position 5 and again goes for 12 characters
53) Right padding the name of department
54) Rpad
55) RPAD() with number column
56) RTRIM(x [, trim_string]) trim characters from the right of x
57) Rtrim removes a set of characters from the right of a string
58) Select ascii(a), ascii(z),ascii(A), ascii(Z),ascii(ABC), chr(77)
59) Select replace (beer bucket,beer) as replace_2
60) Select replace (beer bucket,beer,milk) as replace_1
61) Soundex converts a string to a code value
62) SOUNDEX(x) get a string containing the phonetic representation of x
63) StringString
64) SUBSTR and INSTR used together
65) Substr birthday
66) Substr retrieves a portion of the string
67) Substr(,) and substr(,,)
68) Substr(date value,8)+16
69) SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start
70) The REPLACE Function
71) The SUBSTR Function
72) The TRIM Function
73) Translate vs replace
74) TRIM (both from String with blanks )
75) TRIM from both sides
76) TRIM Leading and Trailing Zeroes
77) TRIM(leading F from FABCDEF)
78) TRIM(trailing r from Real water)
79) Trim leading and ending letters
80) Trim letter S from both sides
81) Triming leading H from employee last name
82) Unistr(0300)
83) UPPER(x) converts the letters in x to uppercase
84) Use both lpad() and rpad() to create a string
85) Use CHR() function to create special char
86) Use dates in INSTR functions
87) Use INITCAP() with table column
88) Use length() function three times in a select statement
89) Use Length() to deal with number column
90) Use rpad function with define column default value
91) Use rpad to format a report
92) Use RPAD to format cursor variable
93) Use rpad to represent the level
94) Use upper() function in where clause
95) Using SUBSTR and INSTR together
96) Vsize returns the storage size of a string in Oracle